package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.X_M_Inventory;
import org.compiere.model.X_M_InventoryEmployee;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRJob;
import org.eevolution.model.X_HR_Employee;
import org.utils.AnalogConvertor_RU;
import extend.org.compiere.utils.Util;

public class MM_Form_401 extends SvrProcess {
	//Form_401_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_M_Inventory_ID;
	
	enum mt {FE, IC};
	/** */
	String ResultPath;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("M_Inventory_ID")) 
				p_M_Inventory_ID = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_401_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xls");
			try { 
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 10) + (++pathI).toString() + ")";  
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" +(++pathI).toString() + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xls";
		
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Style
		CellStyle cs = wb.createCellStyle();
		cs.setBorderBottom((short)1);
		cs.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		
		CellStyle csp = wb.createCellStyle();
		csp.setBorderBottom((short)1);
		csp.setBorderLeft((short)1);
		csp.setBorderRight((short)1);
		csp.setBorderTop((short)1);
		csp.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		csp.setWrapText(true);
		csp.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		csp.setAlignment(CellStyle.ALIGN_CENTER);
		
		sheet.getRow(7).getCell(0).setCellValue(MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		
		X_M_Inventory inv = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT M_Inventory_ID, M_DateOf_Order, M_NumberOf_Order FROM adempiere.M_Inventory " + 
					"WHERE M_Inventory.M_Inventory_ID = " + p_M_Inventory_ID;
		
		try {
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				inv = new X_M_Inventory(m_ctx, rs.getInt(1), get_TrxName());
				
				sheet.getRow(10).getCell(29).setCellValue(inv.getDocumentNo());
				
				sheet.getRow(11).getCell(15).setCellValue((new SimpleDateFormat("d").format(inv.getMovementDate()))); 
				sheet.getRow(11).getCell(18).setCellValue((new SimpleDateFormat("MMMMM").format(inv.getMovementDate())));
				sheet.getRow(11).getCell(24).setCellValue((new SimpleDateFormat("Y").format(inv.getMovementDate())));
				
				sheet.getRow(16).getCell(18).setCellValue(rs.getDate(2) == null ? "" : (new SimpleDateFormat("d").format(rs.getDate(2)))); //.getDate()
				sheet.getRow(16).getCell(21).setCellValue(rs.getDate(2) == null ? "" : (new SimpleDateFormat("MMMMM").format(rs.getDate(2))));
				sheet.getRow(16).getCell(27).setCellValue(rs.getDate(2) == null ? "" : (new SimpleDateFormat("Y").format(rs.getDate(2))));
				sheet.getRow(16).getCell(32).setCellValue(rs.getDate(3) == null ? "" : rs.getString(3));
				
				sheet.getRow(45).getCell(16).setCellValue((new SimpleDateFormat("d").format(inv.getMovementDate())));
				sheet.getRow(45).getCell(19).setCellValue((new SimpleDateFormat("MMMMM").format(inv.getMovementDate())));
				sheet.getRow(45).getCell(25).setCellValue((new SimpleDateFormat("Y").format(inv.getMovementDate())));
				
				sheet.getRow(87).getCell(1).setCellValue((new SimpleDateFormat("d").format(inv.getMovementDate())));
				sheet.getRow(87).getCell(4).setCellValue((new SimpleDateFormat("MMMMM").format(inv.getMovementDate())));
				sheet.getRow(87).getCell(10).setCellValue((new SimpleDateFormat("Y").format(inv.getMovementDate())));
				
				sheet.getRow(90).getCell(1).setCellValue((new SimpleDateFormat("d").format(inv.getMovementDate())));
				sheet.getRow(90).getCell(4).setCellValue((new SimpleDateFormat("MMMMM").format(inv.getMovementDate())));
				sheet.getRow(90).getCell(10).setCellValue((new SimpleDateFormat("Y").format(inv.getMovementDate())));
			}
			
			X_M_InventoryEmployee invemp = null;
			PreparedStatement pstmtemp = null;
			ResultSet rsemp = null;
			String sqlemp = "SELECT M_InventoryEmployee_ID, M_Inventory_MemberType FROM adempiere.M_InventoryEmployee " + 
							"WHERE M_Inventory_ID = " + p_M_Inventory_ID;
			
			try {
				pstmtemp = DB.prepareStatement(sqlemp.toString(), get_TrxName());
				rsemp = pstmtemp.executeQuery();
				
				while (rsemp.next()){
					invemp = new X_M_InventoryEmployee(m_ctx, rsemp.getInt(1), get_TrxName());
					if (rsemp.getString(2).equals("FE")){
						sheet.getRow(22).getCell(24).setCellValue((new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).getName()));
						
						sheet.getRow(43).getCell(8).setCellValue((new MHRJob(m_ctx, X_HR_Employee.getHR_Employee(invemp.getC_BPartner_ID(), (new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).isActive())).getHR_Job_ID(), get_TrxName()).getName()));
						sheet.getRow(43).getCell(25).setCellValue((new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).getName()));
						
						sheet.getRow(86).getCell(17).setCellValue((new MHRJob(m_ctx, X_HR_Employee.getHR_Employee(invemp.getC_BPartner_ID(), (new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).isActive())).getHR_Job_ID(), get_TrxName()).getName()));
						sheet.getRow(86).getCell(38).setCellValue((new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).getName()));
					
					} else if (rsemp.getString(2).equals("IC")){
						sheet.getRow(18).getCell(18).setCellValue((new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).getName()));
						
						sheet.getRow(88).getCell(25).setCellValue((new MHRJob(m_ctx, X_HR_Employee.getHR_Employee(invemp.getC_BPartner_ID(), (new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).isActive())).getHR_Job_ID(), get_TrxName()).getName()));
						sheet.getRow(88).getCell(38).setCellValue((new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).getName()));
					}
				}
				
				//first members
				int row = 11;
				rsemp = null;
				rsemp = pstmtemp.executeQuery();
				int a = 0;
				while (rsemp.next()) {
					invemp = new X_M_InventoryEmployee(m_ctx, rsemp.getInt(1), get_TrxName());
					if (rsemp.getString(2).equals("MC") || rsemp.getString(2).equals("CC")){
						row += 2;
						if (a > 0) {
							sheet.shiftRows(row, row + 131, 2);
							for(int j = row; j <= row + 1; j++){
								R = sheet.createRow(j);
								R.setHeightInPoints((float) 12.75);
								for(int i = 0; i <= 32; i++){
									C = R.createCell(i);
									sheet.getRow(row).getCell(i).setCellStyle(cs);
								}
							}
							sheet.getRow(row + 1).getCell(11).setCellValue("должность");
							sheet.getRow(row + 1).getCell(22).setCellValue("Ф.И.О.");
						}
						
						sheet.getRow(row).getCell(22).setCellValue((new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).getName()));
						sheet.getRow(row).getCell(9).setCellValue((new MHRJob(m_ctx, X_HR_Employee.getHR_Employee(invemp.getC_BPartner_ID(), (new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).isActive())).getHR_Job_ID(), get_TrxName()).getName()));
						a++;
						
					}
				} 
				
				if (rsemp.isFirst()) row += 2;
				
				//tables
				PreparedStatement psinv = null;
				ResultSet rsinv = null;
				String sqlinv = " SELECT p.Name AS Product " + 
									" , p.Value AS ProductNo " + 
									" , uom.Name AS UOM " + 
									" , CASE	WHEN il.inventorytype = 'D'::bpchar AND il.qtycount > il.qtybook THEN fa.amtacctdr / abs(il.qtycount - il.qtybook) " + 
												" WHEN il.inventorytype = 'D'::bpchar AND il.qtycount < il.qtybook THEN fa.amtacctcr / abs(il.qtycount - il.qtybook) " + 
												" ELSE NULL::numeric " + 
									" END AS ColPrice " + 
									" , il.QtyCount " + 
									" , CASE	WHEN il.inventorytype = 'D' AND il.qtycount < il.qtybook THEN ((fa.AmtAcctCr / abs(il.QtyCount - il.QtyBook)) * QtyCount) " + 
												" WHEN il.inventorytype = 'D' AND il.qtycount > il.qtybook THEN ((fa.AmtAcctDr / abs(il.QtyCount - il.QtyBook)) * QtyCount) " +  
									" END AS SummaCount " + 
									" , il.QtyBook " + 
									" , CASE	WHEN il.inventorytype = 'D' AND il.QtyCount < il.QtyBook THEN ((fa.AmtAcctCr / abs(il.QtyCount - il.QtyBook)) * QtyBook) " + 
												" WHEN il.inventorytype = 'D' AND il.QtyCount > il.QtyBook THEN ((fa.AmtAcctDr / abs(il.QtyCount - il.QtyBook)) * QtyBook) " +  
									" END AS SummaBook " + 
									" , CASE	WHEN il.inventorytype = 'D' AND il.qtycount < il.qtybook THEN abs(il.QtyCount - il.QtyBook) " + 
												" ELSE NULL::numeric " + 
									" END AS QtyJetispeu " + 
									" , CASE	WHEN il.inventorytype = 'D' AND il.qtycount < il.qtybook THEN (fa.AmtAcctCr / abs(il.QtyCount - il.QtyBook)) * abs(il.QtyCount - il.QtyBook) " + 
												" ELSE NULL::numeric " + 
									" END AS SummaJetispeu " + 
									" , CASE	WHEN il.inventorytype = 'D' AND il.qtycount > il.qtybook THEN il.QtyCount - il.QtyBook " + 
												" ELSE NULL::numeric " + 
									" END AS QtyArtyq " + 
									" , CASE	WHEN il.inventorytype = 'D' AND il.qtycount > il.qtybook THEN (fa.AmtAcctDr / (il.QtyCount - il.QtyBook)) * (il.QtyCount - il.QtyBook) " + 
												" ELSE NULL::numeric " + 
									" END AS SummaArtyq " + 
								" FROM Fact_Acct fa " + 
								" INNER JOIN C_ElementValue ev ON ev.C_ElementValue_ID = fa.Account_ID " +  
								" INNER JOIN M_Inventory i ON i.M_Inventory_ID = fa.Record_ID " + 
								" INNER JOIN M_InventoryLine il ON (il.M_InventoryLine_ID = fa.Line_ID AND il.InventoryType = 'D') " +  
								" INNER JOIN M_Product p ON p.M_Product_ID = il.M_Product_ID " +  
								" INNER JOIN C_UOM_Trl uom ON uom.C_UOM_ID = p.C_UOM_ID " +  
								" WHERE fa.AD_Table_ID = 321 " + 
									" AND fa.Counter < fa.Reference " + 
									" AND i.M_Inventory_ID = " + p_M_Inventory_ID;
				
				row += 41;
				int row2 = row + 8;
				int No = 0;
				double ortaqQtyCount = 0;
				double ortaqSummaCount = 0;
				try {
					psinv = DB.prepareStatement(sqlinv.toString(), get_TrxName());
					rsinv = psinv.executeQuery();
					
					while (rsinv.next()) 
					{
						//First table
						//shifting and merging
						No++;
						row++;
						sheet.shiftRows(row, row + 77, 1);
						sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 2));
						sheet.addMergedRegion(new CellRangeAddress(row, row, 3, 9));
						sheet.addMergedRegion(new CellRangeAddress(row, row, 10, 16));
						sheet.addMergedRegion(new CellRangeAddress(row, row, 17, 22));
						sheet.addMergedRegion(new CellRangeAddress(row, row, 23, 29));
						sheet.addMergedRegion(new CellRangeAddress(row, row, 30, 34));
						sheet.addMergedRegion(new CellRangeAddress(row, row, 35, 42));
						R = sheet.createRow(row);
						R.setHeightInPoints((float) 12.75);
						for(int i = 0; i <= 42; i++){
							C = R.createCell(i);
							sheet.getRow(row).getCell(i).setCellStyle(csp);
						}
						
						sheet.getRow(row).getCell(0).setCellValue(No);
						sheet.getRow(row).getCell(3).setCellValue(rsinv.getString("Product"));
						sheet.getRow(row).getCell(10).setCellValue(rsinv.getString("ProductNo"));
						sheet.getRow(row).getCell(17).setCellValue(rsinv.getString("UOM"));
						sheet.getRow(row).getCell(23).setCellValue(rsinv.getFloat("ColPrice"));
						sheet.getRow(row).getCell(30).setCellValue(rsinv.getFloat("QtyCount"));
						ortaqQtyCount += rsinv.getFloat("QtyCount");
						sheet.getRow(row).getCell(35).setCellValue(rsinv.getFloat("SummaCount"));
						ortaqSummaCount += rsinv.getFloat("SummaCount");
						
						//Second table
						//shifting and merging
						row2 += 2;
						sheet.shiftRows(row2, row2 + 75, 1);
						sheet.addMergedRegion(new CellRangeAddress(row2, row2, 0, 3));
						sheet.addMergedRegion(new CellRangeAddress(row2, row2, 4, 10));
						sheet.addMergedRegion(new CellRangeAddress(row2, row2, 11, 14));
						sheet.addMergedRegion(new CellRangeAddress(row2, row2, 15, 21));
						sheet.addMergedRegion(new CellRangeAddress(row2, row2, 22, 25));
						sheet.addMergedRegion(new CellRangeAddress(row2, row2, 26, 32));
						sheet.addMergedRegion(new CellRangeAddress(row2, row2, 33, 38));
						sheet.addMergedRegion(new CellRangeAddress(row2, row2, 39, 42));
						R = sheet.createRow(row2);
						R.setHeightInPoints((float) 12.75);
						for(int i = 0; i <= 42; i++){
							C = R.createCell(i);
							sheet.getRow(row2).getCell(i).setCellStyle(csp);
						}	
						
						sheet.getRow(row2).getCell(0).setCellValue(rsinv.getFloat("QtyBook"));
						sheet.getRow(row2).getCell(4).setCellValue(rsinv.getFloat("SummaBook"));
						sheet.getRow(row2).getCell(11).setCellValue(rsinv.getFloat("QtyJetispeu"));
						sheet.getRow(row2).getCell(15).setCellValue(rsinv.getFloat("SummaJetispeu"));
						sheet.getRow(row2).getCell(22).setCellValue(rsinv.getFloat("QtyArtyq"));
						sheet.getRow(row2).getCell(26).setCellValue(rsinv.getFloat("SummaArtyq"));
						sheet.getRow(row2).getCell(39).setCellValue(No);
					}

					sheet.getRow(row2 + 6).createCell(13);
					sheet.getRow(row2 + 6).getCell(13).setCellStyle(cs);
					sheet.getRow(row2 + 6).getCell(13).setCellValue(AnalogConvertor_RU.toString(No));
					
					//Faktilik sany men summasy negizi inventarizatsia istelgennen keiin qoimadagy qaldyqtan alynu kerek 
					sheet.getRow(row2 + 8).createCell(20);
					sheet.getRow(row2 + 8).getCell(20).setCellStyle(cs);
					String str = AnalogConvertor_RU.toString(ortaqQtyCount);
					str = str.substring(0, str.indexOf("тенге"));
					sheet.getRow(row2 + 8).getCell(20).setCellValue(str);
					
					sheet.getRow(row2 + 10).createCell(11);
					sheet.getRow(row2 + 10).getCell(11).setCellStyle(cs);
					sheet.getRow(row2 + 10).getCell(11).setCellValue(AnalogConvertor_RU.toString(ortaqSummaCount));
				}
				catch (Exception e) {
					log.log(Level.SEVERE, sqlinv.toString(), e);
				}
				finally {
					DB.close(rsinv, psinv);
					rsinv = null;
					psinv = null;
				}
					
				//second members
				rsemp = null;
				rsemp = pstmtemp.executeQuery();
				row2 += 14;
				int r = 0;
				int c = 0;
				while (rsemp.next()){
					invemp = new X_M_InventoryEmployee(m_ctx, rsemp.getInt(1), get_TrxName());
					if (rsemp.getString(2).equals("CC")) {
						sheet.getRow(row2 - r - 1).getCell(28).setCellValue((new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).getName()));
						sheet.getRow(row2 - r - 1).getCell(10).setCellValue((new MHRJob(m_ctx, X_HR_Employee.getHR_Employee(invemp.getC_BPartner_ID(), (new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).isActive())).getHR_Job_ID(), get_TrxName()).getName()) != null ? (new MHRJob(m_ctx, X_HR_Employee.getHR_Employee(invemp.getC_BPartner_ID(), (new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).isActive())).getHR_Job_ID(), get_TrxName()).getName()) : " ");
					} else if (rsemp.getString(2).equals("MC")) {
						row2+=2;
						r += 2;
						if (c > 0) {
							sheet.shiftRows(row2, row2 + 52, 2);
							for(int j = row2; j <= row2 + 1; j++){
								R = sheet.createRow(j);
								R.setHeightInPoints((float) 13);
								for(int i = 7; i <= 32; i++){
									C = R.createCell(i);
									sheet.getRow(row2).getCell(i).setCellStyle(cs);
								}
							}
							sheet.getRow(row2 + 1).getCell(9).setCellValue("должность");
							sheet.getRow(row2 + 1).getCell(25).setCellValue("Ф.И.О.");
						}
						
						sheet.getRow(row2).getCell(25).setCellValue((new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).getName()));
						sheet.getRow(row2).getCell(8).setCellValue((new MHRJob(m_ctx, X_HR_Employee.getHR_Employee(invemp.getC_BPartner_ID(), (new MBPartner(m_ctx, invemp.getC_BPartner_ID(), get_TrxName()).isActive())).getHR_Job_ID(), get_TrxName()).getName()));
						c++;
					}
				}
				
				if (rsemp.isFirst()) row2+=2;
				sheet.getRow(row2 + 4).createCell(2);
				sheet.getRow(row2 + 4).getCell(2).setCellValue(1);
				sheet.getRow(row2 + 4).createCell(8);
				sheet.getRow(row2 + 4).getCell(8).setCellValue(No);
			}
			catch (Exception e) {
				log.log(Level.SEVERE, sqlemp.toString(), e);
			}
			finally {
				DB.close(rsemp, pstmtemp);
				rsemp = null;
				pstmtemp = null;
			}
		}
		catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
	
		//---------------------------------------------------------------
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		// Open the file with the application associated
					Util.launchFile(new File(ResultPath));

		
		return Msg.translate(m_ctx, "Success");
	}

}
